//201902104003邵潇漩
package bysj.dao;
import bysj.domain.*;
import bysj.service.DegreeService;
import bysj.service.DepartmentService;
import bysj.service.ProfTitleService;
import bysj.service.UserService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public final class TeacherDao {
    private static TeacherDao teacherDao = new TeacherDao();

    /*static {

        try {
            ProfTitle assProf = ProfTitleDao.getInstance().find(2);
            ProfTitle lecture = ProfTitleDao.getInstance().find(3);

            Degree phd = DegreeDao.getInstance().find(1);
            Degree master = DegreeDao.getInstance().find(2);


            Department misDept = DepartmentService.getInstance().find(2);

            teachers = new TreeSet<Teacher>();
            Teacher teacher = new Teacher(1, "苏同", assProf, phd, misDept);
            teachers.add(teacher);
            teachers.add(new Teacher(2, "刘霞", lecture, phd, misDept));
            teachers.add(new Teacher(3, "王方", assProf, phd, misDept));
            teachers.add(new Teacher(4, "刘峰", assProf, master, misDept));
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }*/

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        return teacherDao;
    }

    public Collection<Teacher> findAll(Connection connection , List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<Teacher> teachers = new TreeSet<Teacher>();
        int totalNum = CommonDao.getInstance().count(connection,"teacher");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //获得查询的预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //执行sql查询语句并获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //遍历结果集，根据元组创建对象，并添加到集合中
        while(resultSet.next()){
            teachers.add(new Teacher(resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("no"),
                            DepartmentService.getInstance().find(resultSet.getInt("department_id"))
                    ));
        }
        JdbcHelper.close(resultSet,pstmt);
        return teachers;
    }
    public Teacher find(Connection conn,Integer id) throws SQLException {
        Teacher desiredTeacher = null;
        //创建sql语句
        String selectTeacher_sql="select * from teacher WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt=conn.prepareStatement(selectTeacher_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //执行SQL语句并获得结果集对象
        ResultSet resultSet=pstmt.executeQuery();
        //若结果集仍然有下一条记录，则执行循环体
        while (resultSet.next()) {
            String name=resultSet.getString("name");
            String no = resultSet.getString("no");
            int departmentId=resultSet.getInt("department_id");
            int profTitleId=resultSet.getInt("profTitle_id");
            int degreeId=resultSet.getInt("degree_id");
            Department department= DepartmentService.getInstance().find(departmentId);
            ProfTitle profTitle= ProfTitleService.getInstance().find(profTitleId);
            Degree degree= DegreeService.getInstance().find(degreeId);
            desiredTeacher=new Teacher(id,name,no,profTitle,degree,department);
        }
        //关闭连接
        JdbcHelper.close(null,pstmt);

        //返回school对象
        return desiredTeacher;
    }

    public boolean update(Teacher teacher, Connection connection) throws SQLException{
        //创建sql语句
        String updateTeacher_sql = "UPDATE teacher SET name=?, no=?, department_id=?,profTitle_id=?, degree_id=? WHERE id=? ";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateTeacher_sql);
        //为预编译参数赋值
        pstmt.setString(1,teacher.getName());
        pstmt.setString(2,teacher.getNo());
        pstmt.setInt(3,teacher.getDepartment().getId());
        pstmt.setInt(4,teacher.getTitle().getId());
        pstmt.setInt(5,teacher.getDegree().getId());
        pstmt.setInt(6,teacher.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }

    /*public boolean add(Teacher teacher) throws SQLException{
        return teachers.add(teacher);
    }
    public int add(Teacher teacher, Connection connection)throws SQLException{
        //根据连接对象准备语句对象
        PreparedStatement pstmt=connection.prepareStatement("INSERT INTO teacher"+" (name,no,department_id)"+" VALUES(?,?,?)", Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1,teacher.getName());
        pstmt.setString(2,teacher.getNo());
        pstmt.setInt(3,teacher.getDepartment().getId());
        int affactedRowNum=pstmt.executeUpdate();
        int idOfAddedTeacher=0;
        //获得生成的主键集合
        ResultSet resultSet=pstmt.getGeneratedKeys();
        if (resultSet.next()){
            //读取第一个主键
            idOfAddedTeacher=resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,pstmt);
        //本方法是事务的一部分，不能释放资源
        return idOfAddedTeacher;
    }*/

    public void add(Teacher teacher, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO teacher " +
                                " (id, name,no, profTitle_id, degree_id, department_id)" +
                                " VALUES (?, ?, ?,?,?,?)");
        preparedStatement.setInt(1, teacher.getId());
        preparedStatement.setString(2, teacher.getName());
        preparedStatement.setString(3, teacher.getNo());
        preparedStatement.setInt(4, teacher.getTitle().getId());
        preparedStatement.setInt(5, teacher.getDegree().getId());
        preparedStatement.setInt(6, teacher.getDepartment().getId());
        preparedStatement.executeUpdate();
        //本方法是事务的一部分，不能释放资源
        JdbcHelper.close(null,preparedStatement);
    }
    public boolean delete(Connection conn,Integer id) throws SQLException{
        //创建sql语句
        String deleteDepartment_sql = "DELETE FROM teacher WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = conn.prepareStatement(deleteDepartment_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译语句的executeUpdate方法,获得删除的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null,pstmt);
        //如果删除记录的行数不为0，返回true,否则返回false
        return affectedRowNum>0;
    }

    public boolean delete(Connection connection,Teacher teacher) throws SQLException{
        return this.delete(connection,teacher.getId());
    }
    public int countAll(Department department,Connection conn) throws SQLException{
        String str_sql="SELECT COUNT(id) as cnt_by_department_id FROM teacher WHERE department_id=?";
        PreparedStatement pstmt=conn.prepareStatement(str_sql);
        //为预编译参数赋值
        pstmt.setInt(1, department.getId());
        ResultSet resultSet=pstmt.executeQuery();
        int count=0;
        while (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JdbcHelper.close(resultSet,pstmt);
        return count;
    }
}
